package com.ruoyi.bi.service.impl.model;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.bi.domain.common.RequestStatus;
import com.ruoyi.bi.domain.common.Result;
import com.ruoyi.bi.domain.model.DataSource;
import com.ruoyi.bi.mapper.model.DataSourceMapper;
import com.ruoyi.bi.service.bireport.ModelCacheService;
import com.ruoyi.bi.service.model.DataSourceService;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.naming.Context;
import javax.naming.InitialContext;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @author anan.
 * @version 1.0
 * @create 2021-04-24 09-33
 */
@Service
public class DataSourceServiceImpl extends ServiceImpl<DataSourceMapper,DataSource> implements DataSourceService {

    private Logger log = Logger.getLogger(DataSourceService.class);

    @Autowired
    private DataSourceMapper mapper;

    @Autowired
    private DataSourceServiceImpl dataSourceService;

    @Autowired
    private ModelCacheService cacheService;

    @Override
    public List<DataSource> listDataSource(){
        return mapper.listDataSource();
    }

    @Override
    public void insertDataSource(DataSource ds){
        ds.setDsid(UUID.randomUUID().toString().replaceAll("-", ""));
        if("jndi".equals(ds.getUse())){
            ds.setDsname(ds.getJndiName());
        }
        mapper.insertDataSource(ds);
    }

    @Override
    public void updateDataSource(DataSource ds){
        if("jndi".equals(ds.getUse())){
            ds.setDsname(ds.getJndiName());
        }
        mapper.updateDataSource(ds);
        /** 清除缓存 */
        this.cacheService.removeDsource(ds.getDsid());
    }

    @Override
    public void deleteDataSource(String dsid){
        mapper.deleteDataSource(dsid);
        /** 清除缓存 */
        this.cacheService.removeDsource(dsid);
    }

    @Override
    public DataSource getDataSource(String dsid){
        return mapper.getDataSource(dsid);
    }

    @Override
    public Result testJNDI(DataSource ds){
        Result ret = new Result();
        Connection con = null;
        try{
            Context ctx = new InitialContext();
            String strLookup = "java:comp/env/"+ds.getJndiName();
            javax.sql.DataSource sds = (javax.sql.DataSource) ctx.lookup(strLookup);
            con = sds.getConnection();
            if (con != null){
                ret.setResult(RequestStatus.SUCCESS.getStatus());
            }else{
                ret.setResult(RequestStatus.FAIL_FIELD.getStatus());
            }
        }catch (Exception e) {
            log.error("JNDI测试出错", e);
            ret.setResult(RequestStatus.FAIL_FIELD.getStatus());
            ret.setMsg(e.getMessage());
        }finally{
            if(con != null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return ret;
    }

    @Override
    public Connection getJDBC(DataSource ds) throws Exception{
        try {
            Connection conn = null;
            Class.forName(ds.getClazz()).newInstance();
            conn= DriverManager.getConnection(ds.getLinkUrl(), ds.getLinkName(), ds.getLinkPwd());
            return conn;
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public Connection getJndi(DataSource ds) throws Exception {
        Connection con = null;
        try {
            Context ctx = new InitialContext();
            String strLookup = "java:comp/env/"+ds.getJndiName();
            javax.sql.DataSource sds =(javax.sql.DataSource) ctx.lookup(strLookup);
            con = sds.getConnection();
        }catch(Exception ex){
            ex.printStackTrace();
            throw ex;
        }
        return con;
    }

	/**
	 * 测试前端传来datasource连接数据库是否成功
	 * @param ds
	 * @return
	 */
	@Override
    public Result testDataSource(DataSource ds) {
        Result ret = new Result();
        /** String clazz = ds.getClazz(); */
        String clazz = getClazz(ds);
        Connection conn = null;
        try {
            Class.forName(clazz).newInstance();
            conn= DriverManager.getConnection(ds.getLinkUrl(), ds.getLinkName(),  ds.getLinkPwd());
            if(conn != null){
                ret.setResult(RequestStatus.SUCCESS.getStatus());
            }else{
                ret.setResult(RequestStatus.FAIL_FIELD.getStatus());
            }
        } catch (Exception e) {
            log.error("JDBC测试出错。", e);
            ret.setResult(RequestStatus.FAIL_FIELD.getStatus());
            ret.setMsg(e.getMessage());
        }finally{
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return ret;
    }

    @Override
    public List<Map<String, Object>> listTables(String dsid, String searchTname) throws Exception{
        DataSource ds = dataSourceService.getById(dsid);
        final List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
        Connection conn = null;
        try {
            if(ds.getUse().equals("jndi")){
                conn = this.getJndi(ds);
            }else if(ds.getUse().equals("jdbc")){
                conn = this.getJDBC(ds);
            }
            String schem = null;
            if("oracle".equals(ds.getLinkType())){
                schem = ds.getLinkName().toUpperCase();
            }
            if("postgresql".equals(ds.getLinkType())) {
                if (ds.getLinkUrl().toLowerCase().indexOf("schema") > 0) {
                    String currentSchema = ds.getLinkUrl().toLowerCase().substring(ds.getLinkUrl().toLowerCase().lastIndexOf("schema"));
                    int start = currentSchema.indexOf("=");
                    int end = currentSchema.indexOf("&");
                    schem = currentSchema.substring(start + 1,end > 0 ? end : currentSchema.length());
                }
            }
            String catalog = null;
            if("mysql".equals(ds.getLinkType())) {
                catalog = conn.getCatalog();
            }
            ResultSet tbs = conn.getMetaData().getTables(catalog, schem, (searchTname!=null&&searchTname.length() > 0) ?("%"+searchTname+"%"):"%", new String[]{"TABLE","VIEW"});
            while(tbs.next()){
                Map<String, Object> m = new HashMap<String, Object>();
                String tname = tbs.getString("TABLE_NAME");
                if (StringUtils.isNotBlank(schem)) {
                    tname = schem + "." + tname;
                }
                m.put("id", tname);
                m.put("text", tname);
                m.put("icon", "fa fa-table");
                ret.add(m);
            }
            tbs.close();

            /**
             String qsql = null;
             if("mysql".equals(ds.getLinkType())){
             qsql = showTables_mysql;
             }else if("oracle".equals(ds.getLinkType())){
             qsql = showTables_oracle;
             }else if("sqlserver".equals(ds.getLinkType())){
             qsql = showTables_sqlser;
             }else if("db2".equals(ds.getLinkType())){
             qsql = ConstantsEngine.replace(showTables_db2, ds.getLinkName());
             }else if("postgresql".equals(ds.getLinkType())){
             qsql = ConstantsEngine.replace(showTables_psql, ds.getLinkName());
             }else if("hive".equals(ds.getLinkType())){
             qsql = showTables_hive;
             }else if("kylin".equals(ds.getLinkType())){
             qsql = showTables_kylin;
             }
             ResultSet tbs = conn.getMetaData().getTables(null, null, "%", new String[]{"TABLE"});


             while(tbs.next()){
             for(int i=0; i<tbs.getMetaData().getColumnCount(); i++){
             String col = tbs.getMetaData().getColumnLabel(i + 1);
             System.out.println( col + " === " + tbs.getString(col));
             }
             }
             tbs.close();
             PreparedStatement ps = conn.prepareStatement(qsql);
             ResultSet rs = ps.executeQuery();
             while(rs.next()){
             Map<String, Object> m = new HashMap<String, Object>();
             copyData(rs, m);
             ret.add(m);
             }
             rs.close();
             ps.close();
             **/
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("sql 执行报错.");
        }finally{
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return ret;
    }

    public String getClazz(DataSource ds){
        String clazz = "";
        switch (ds.getLinkType()) {
            case "db2":
                clazz = "com.ibm.db2.jcc.DB2Driver";
                break;
            case "hive":
                clazz = "org.apache.hive.jdbc.HiveDriver";
                break;
            case "kylin":
                clazz = "org.apache.kylin.jdbc.Driver";
                break;
            case "mysql":
                clazz = "com.mysql.cj.jdbc.Driver";
                break;
			case "oracle":
				clazz = "oracle.jdbc.driver.OracleDriver";
				break;
			case "oracleName":
				clazz = "oracle.jdbc.OracleDriver";
                break;
            case "postgresql":
                clazz = "org.postgresql.Driver";
                break;
            case "sqlser":
                clazz = "net.sourceforge.jtds.jdbc.Driver";
                break;
            case "sqlite":
                clazz = "org.sqlite.JDBC";
                break;
            default :
                break;
        }
        return clazz;
    }


}
